/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ticketmaster.dao;

import java.sql.ResultSet;
import javax.sql.RowSet;
import ticketmaster.dataobject.BookedTicketView;
import ticketmaster.dataobject.Customer;
import ticketmaster.dataobject.Event;
import ticketmaster.dataobject.Venue;

/**
 *
 * @author Varadha
 */
public class BookedTicketViewDAO extends BaseEntityDAO
{
    public RowSet getEventDay(Event[] event)
    {

        String where;
        where = "( "+event[0].getEventID();
        
        for(int i=1; i<event.length; i++)
            where = where + " , "+event[i].getEventID();


        where = where + " )";

        String sql = "select DATE(bookeddate), eventName, sum(price) " +
                "from BookedTicketView where eventID in" + where
                +" group by DATE(bookeddate), eventID order by bookedday";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getEventMonth(Event[] event)
    {
        String where;
        where = "( "+event[0].getEventID();

        for(int i=1; i<event.length; i++)
            where = where + " , "+event[i].getEventID();


        where = where + " )";

        String sql = "select bookedmonth, bookedyear ,eventName ,sum(price)" +
                " from BookedTicketView where eventID in " + where +" group by bookedmonth, bookedyear, " +
                "eventID " +
                " order by bookedmonth";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getEventYear(Event[] event)
    {
        String where;
        where = "( "+event[0].getEventID();

        for(int i=1; i<event.length; i++)
            where = where + " , "+event[i].getEventID();


        where = where + " )";

        String sql = "select bookedyear, eventName , sum(price) " +
                "from BookedTicketView where eventID in " + where +" group by bookedyear, eventID" +
                " order by bookedyear";
        RowSet rs = query(sql);
        return rs;
    }
    
    public RowSet getAlltime(Event[] event)
    {
        String where;
        where = "( "+event[0].getEventID();

        for(int i=1; i<event.length; i++)
            where = where + " , "+event[i].getEventID();


        where = where + " )";

        String sql = "select eventName , sum(price)" +
                "from BookedTicketView where eventID in " + where +" group by eventID"+
                " order by eventID";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getVenueDay(Venue[] venue)
    {

        String where;
        where = "( "+venue[0].getVenueID();

        for(int i=1; i<venue.length; i++)
            where = where + " , "+venue[i].getVenueID();


        where = where + " )";

        String sql = "select DATE(bookeddate), venueName ,sum(price) " +
                "from BookedTicketView where venueID  in " + where +" group by DATE(bookeddate), " +
                "venueID"+
                " order by DATE(bookeddate)";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getVenueMonth(Venue[] venue)
    {
        String where;
        where = "( "+venue[0].getVenueID();

        for(int i=1; i<venue.length; i++)
            where = where + " , "+venue[i].getVenueID();


        where = where + " )";
        String sql = "select bookedmonth, bookedyear, venueName ,sum(price)" +
                " from BookedTicketView where venueID in " +where+" group by bookedmonth," +
                "bookedyear, venueID"
                +
                " order by bookedmonth";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getVenueYear(Venue[] venue)
    {

        String where;
        where = "( "+venue[0].getVenueID();

        for(int i=1; i<venue.length; i++)
            where = where + " , "+venue[i].getVenueID();


        where = where + " )";

        String sql = "select bookedyear, venueName ,sum(price) " +
                " from BookedTicketView where venueID in" + where +" group by " +
                "bookedyear, venueID"
                +
                " order by bookedyear";

        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getCustomerYear(Customer[] customer)
    {
        String where;
        where = "( "+customer[0].getCustomerID();

        for(int i=1; i<customer.length; i++)
            where = where + " , "+customer[i].getCustomerID();


        where = where + " )";

        String sql = "select bookedyear, customerName, sum(price) " +
                "from BookedTicketView where customerID in " + where +" group by bookedyear " +
                " order by bookedyear";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getCustomerMonth(Customer[] customer)
    {
        String where;
        where = "( "+customer[0].getCustomerID();

        for(int i=1; i<customer.length; i++)
            where = where + " , "+customer[i].getCustomerID();


        where = where + " )";

       String sql = "select bookedmonth,bookedyear,customerName, sum(price) " +
                "from BookedTicketView where customerID in " + where +" group by bookedmonth, bookedyear " +
                " order by bookedmonth";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getCustomerDay(Customer[] customer)
    {
        String where;
        where = "( "+customer[0].getCustomerID();

        for(int i=1; i<customer.length; i++)
            where = where + " , "+customer[i].getCustomerID();


        where = where + " )";

        String sql = "select DATE(bookeddate),customerName, sum(price) " +
                "from BookedTicketView where customerID in " + where +" group by DATE(bookeddate) " +
                " order by bookeddate";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getLocationYear(String[] Location)
    {
        String where;
        where = "( \""+Location[0] + "\"";

        for(int i=1; i<Location.length; i++)
            where = where + " , \" "+Location[i] + "\"";


        where = where + " )";

        String sql = "select b.bookedyear, v.location ,sum(b.price),avg(b.price) " +
                "from BookedTicketView b, Venue v where v.location in " + where +" and v.venueid = b.venueid" +
                " group by b.bookedyear, v.location order by b.bookedyear";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getLocationMonth(String[] Location)
    {
        String where;
        where = "( \""+Location[0] + "\"";

        for(int i=1; i<Location.length; i++)
            where = where + " , \" "+Location[i] + "\"";


        where = where + " )";

        String sql = "select b.bookedmonth, b.bookedyear, v.location ,sum(b.price) " +
                "from BookedTicketView b, Venue v where v.location in " + where +" and v.venueid = b.venueid" +
                " group by b.bookedmonth, b.bookedyear, v.location order by b.bookedyear";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getLocationDay(String[] Location)
    {
        String where;
        where = "( \""+Location[0] + "\"";

        for(int i=1; i<Location.length; i++)
            where = where + " , \" "+Location[i] + "\"";


        where = where + " )";

        String sql = "select DATE(b.bookeddate), v.location ,sum(b.price) " +
                "from BookedTicketView b, Venue v where v.location in " + where +" and v.venueid = b.venueid" +
                " group by DATE(b.bookeddate), v.location order by b.bookeddate";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getCustomerRange(Customer[] customer, String frm, String to)
    {
        String where;
        where = "( "+customer[0].getCustomerID();

        for(int i=1; i<customer.length; i++)
            where = where + " , "+customer[i].getCustomerID();


        where = where + " )";

        String between;
        if(frm.equals(to))
            between = "DATE(bookeddate) = \"" + frm;
        else
            between = "DATE(bookeddate) between \""+frm +"\" and \"" +to;

        String sql = "select DATE(bookeddate), customerName, sum(price) " +
                "from BookedTicketView where customerID in " + where +"" +
                " and "+between+ "\"  group by DATE(bookeddate) " +
                " order by DATE(bookeddate)";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getVenueRange(Venue[] venue,String frm, String to)
    {
        String where;
        where = "( "+venue[0].getVenueID();

        for(int i=1; i<venue.length; i++)
            where = where + " , "+venue[i].getVenueID();


        where = where + " )";

        String between;
        if(frm.equals(to))
            between = "DATE(bookeddate) = \"" + frm;
        else
            between = "DATE(bookeddate) between \""+frm +"\" and \"" +to;

        String sql = "select DATE(bookeddate),venueName ,sum(price),avg(price) " +
                "from BookedTicketView where venueID in " + where +" " +
                " and " +between+
                "\" group by DATE(bookeddate), " +
                "venueID"+
                " order by DATE(bookeddate)";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getEventRange(Event[] event,String frm, String to)
    {
        String where;
        where = "( "+event[0].getEventID();

        for(int i=1; i<event.length; i++)
            where = where + " , "+event[i].getEventID();


        where = where + " )";

        String between;
        if(frm.equals(to))
            between = "DATE(bookeddate) = \"" + frm;
        else
            between = "DATE(bookeddate) between \""+frm +"\" and \"" +to;

        String sql = "select DATE(bookeddate), eventName, sum(price)," +
                "avg(price)" +
                "from BookedTicketView where eventID in " + where
                + " and "+between+
                "\" group by DATE(bookeddate), eventID order by DATE(bookeddate)";
        RowSet rs = query(sql);
        return rs;
    }

    public RowSet getLocationrange(String[] Location, String frm, String to)
    {
        String where;
        where = "( \""+Location[0] + "\"";

        for(int i=1; i<Location.length; i++)
            where = where + " , \" "+Location[i] + "\"";

        where = where + " )";
        
        String between;
        if(frm.equals(to))
            between = "DATE(bookeddate) = \"" + frm;
        else
            between = "DATE(bookeddate) between \""+frm +"\" and \"" +to;

        String sql = "select DATE(b.bookeddate), v.location ,sum(b.price) " +
                "from BookedTicketView b, Venue v where v.location in " + where +" and v.venueid = b.venueid" +
                " and "+between+
                "\"group by DATE(b.bookeddate), v.location order by DATE(b.bookeddate)";
        RowSet rs = query(sql);
        return rs;
    }


}
